{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "i=2,str(sheet.cells(x,4).value)\n",
      "i=3,str(sheet.cells(x,4).value)\n",
      "i=4,str(sheet.cells(x,5).value)\n",
      "i=5,保存赁证\n",
      "i=6,str(sheet.cells(x,4).value)\n",
      "i=7,str(sheet.cells(x,5).value)\n",
      "i=8,保存赁证\n",
      "i=9,str(sheet.cells(x,4).value)\n",
      "i=10,str(sheet.cells(x,5).value)\n",
      "i=11,保存赁证\n",
      "i=12,str(sheet.cells(x,4).value)\n",
      "i=13,str(sheet.cells(x,4).value)\n",
      "i=14,str(sheet.cells(x,4).value)\n",
      "i=15,str(sheet.cells(x,5).value)\n",
      "i=16,保存赁证\n",
      "i=17,str(sheet.cells(x,4).value)\n",
      "i=18,str(sheet.cells(x,5).value)\n",
      "i=19,保存赁证\n",
      "i=20,str(sheet.cells(x,4).value)\n",
      "i=21,str(sheet.cells(x,5).value)\n",
      "i=22,str(sheet.cells(x,5).value)\n",
      "i=23,保存赁证\n",
      "i=24,str(sheet.cells(x,4).value)\n",
      "i=25,str(sheet.cells(x,5).value)\n",
      "i=26,保存赁证\n",
      "i=27,str(sheet.cells(x,4).value)\n",
      "i=28,str(sheet.cells(x,5).value)\n",
      "i=29,保存赁证\n",
      "i=30,str(sheet.cells(x,4).value)\n",
      "i=31,str(sheet.cells(x,5).value)\n",
      "i=32,保存赁证\n",
      "i=33,str(sheet.cells(x,4).value)\n",
      "i=34,str(sheet.cells(x,5).value)\n"
     ]
    }
   ],
   "source": [
    "import pyautogui as pag  ##先用pip3 install python3-xlib，\n",
    "#再用pip3 install pyautogui进行安装\n",
    "import pyperclip\n",
    "import xlwings as xw\n",
    "pag.PAUSE = 0.8# pyautogui.PAUSE函数在pyautogui中含义是每一步停留时间间隔，\n",
    "#模块默认值是0.1秒，不过我用的财务软件反应不过来，因此延长了时间设置成了0.5秒。\n",
    "pag.FAILSAFE = True\n",
    "app=xw.App(visible=False,add_book=False)\n",
    "app.display_alerts=False   #不显示Excel消息框\n",
    "app.screen_updating=False  #关闭屏幕更新,可加快宏的执行速度\n",
    "wb = xw.Book(\"凭证录入模块.xlsx\")\n",
    "sheet = wb.sheets[\"录入分录明细\"]\n",
    "info = sheet.used_range # 读取excel中的最大行\n",
    "max_row =info.last_cell.row# 读取excel中的最大行\n",
    "\n",
    "pag.click(766, 365) # 增加凭证\")\n",
    "pag.hotkey('Enter')##回车 \n",
    "pag.hotkey('Enter')##回车 \n",
    "pag.hotkey('Enter')##回车 \n",
    "for x in range(2,max_row+1):\n",
    "    #pyperclip.copy(str(sheet.cell(x,1).value))##复制EXCEL中的摘要\n",
    "    #pag.hotkey('ctrl', 'v')##粘贴单元格\n",
    "    #pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "    #pag.typewrite(str(sheet.cell(x,2).value))##输入科目代码\n",
    "    #pag.hotkey('Tab')##热键Tab到科目代码栏 \n",
    "    if sheet.cells(x,3).value is None: ##Xlwings中，cell后要加S\n",
    "        pag.click(755, 392) # 点击保存\n",
    "        pag.click(766, 365) # 增加凭证\")\n",
    "        pag.hotkey('Enter')##回车 \n",
    "        pag.hotkey('Enter')##回车 \n",
    "        pag.hotkey('Enter')##回车 \n",
    "        print(f\"i={x},保存赁证\")\n",
    "        continue\n",
    "    if sheet.cells(x,3).value==\"贷\":\n",
    "        pag.hotkey('Backspace')\n",
    "        #pag.click(680, 480 + (x%2)*40) ##点击摘要\n",
    "        pyperclip.copy(str(sheet.cells(x,1).value))##复制EXCEL中的摘要\n",
    "        pag.hotkey('ctrl','v')##粘贴单元格\n",
    "        pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "        pag.typewrite(str(sheet.cells(x,2).value))##输入科目代码\n",
    "        pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "        pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "        pag.typewrite(str(sheet.cells(x,5).value))##输入贷方\n",
    "        pag.hotkey('Enter')##回车\n",
    "        pag.hotkey('Backspace')\n",
    "        print(f\"i={x},str(sheet.cells(x,5).value)\")\n",
    "    else:\n",
    "        pag.hotkey('Backspace')\n",
    "       # pag.click(683, 486 + (x%2)*41) ##点击摘要\n",
    "        pyperclip.copy(str(sheet.cells(x,1).value))##复制EXCEL中的摘要\n",
    "        pag.hotkey('ctrl','v')##粘贴单元格\n",
    "        pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "        pag.typewrite(str(sheet.cells(x,2).value))##输入科目代码\n",
    "        pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "        pag.typewrite(str(sheet.cells(x,4).value))##输入借方\n",
    "        pag.hotkey('Enter')##回车\n",
    "        print(f\"i={x},str(sheet.cells(x,4).value)\")\n",
    "        ##    elif str(sheet.cell(x,3).value==\"借\"):\n",
    "        ##pag.hotkey('Tab')###回车 ##热键Tab到科目代码栏\n",
    "        ##pag.typewrite(str(sheet.cell(x,4).value))##输入贷方\n",
    "        #print(f\"i={x},str(sheet.cell(x,4).value)\")\n",
    "wb.close()\n",
    "app.quit()  \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
